IF OBJECT_ID('dbo.RptPCROTurnaroundReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptPCROTurnaroundReport
    IF OBJECT_ID('dbo.RptPCROTurnaroundReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptPCROTurnaroundReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptPCROTurnaroundReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptPCROTurnaroundReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 09/03/2008
--
-- Description: Source of Report, "PCROTurnaroundReport"
--                  In Insight DB Reporting Services
--
-- To allow the ability to measure Associate/Analyst turnaround times for 
-- CSCR contract deliverables, support agreements, and amendments.
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptPCROTurnaroundReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 09/03/08    KC        Created
-- 09/08/08    KC        Finalized. 
-- 09/10/08    KC        Added Median Functionality
-- 09/18/08    KC        Fixed Grouping issue. Bug on calling TST Proc.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptPCROTurnaroundReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @AnalysisType                      VARCHAR(20) = NULL,
     @ManagerType                       VARCHAR(20) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     SET ANSI_WARNINGS OFF
     
     -- Declare Local Variables
     DECLARE   @Error              INT,
               @RowCount           INT,
               @ReturnCode         INT,
               @InstName           VARCHAR(50),
               @DeptName           VARCHAR(50),
               @ManagerId          INT,
               @SubAnalysisType    VARCHAR(40),
               @Median             NUMERIC(12,4)
               
     -- Gonna let the Detail Report do the work of searching...
     DECLARE   @PropsOrDels TABLE
          (AgreementId                  INT,
          FolderNumber                  VARCHAR(20),
          InfoEdPropNumber              VARCHAR(20),
          ManagerType                   VARCHAR(20),
          ManagerId                     INT,
          ManagerName                   VARCHAR(60),
          GroupCode                     VARCHAR(40),
          AnalysisTypeCode              VARCHAR(40),
          AnalysisType                  VARCHAR(40),
          SubAnalysisType               VARCHAR(40),
          SubActivityType               VARCHAR(60),
          ProposalOrDeliverableName     VARCHAR(100),
          PrincipalInvestigator         VARCHAR(60),
          PrimarySponsor                VARCHAR(75),
          SecondarySponsor              VARCHAR(75),
          AgreementType                 VARCHAR(20),
          TransactionType               VARCHAR(30),
          Title                         VARCHAR(50),
          Institution                   VARCHAR(50),
          Department                    VARCHAR(50),
          ProposalOrDeliverableID       VARCHAR(20),
          CompletedDate                 DATETIME,
          PendingDate                   DATETIME,
          DaysToComplete                INT)

     DECLARE   @MeansAndMedians TABLE
          (SubAnalysisType              VARCHAR(40),
          SubAnalysisMean               NUMERIC(12,4),
          SubAnalysisMedian             NUMERIC(12,4))

     DECLARE   @ManagerSubAnalysisTypeMedians TABLE
          (ManagerId                    INT,
          SubAnalysisType               VARCHAR(40),
          SubAnalysisMedian             NUMERIC(12,4))

     DECLARE   @Medians TABLE
          (DayCounts                    NUMERIC(12,4))
     
     -- Check Errors
     SELECT @Error=@@ERROR
     IF @Error != 0 
     BEGIN
          RETURN -1
     END

     -- Execute Detail Report
     INSERT    @PropsOrDels
          (AgreementId,
          FolderNumber,
          InfoEdPropNumber,
          ManagerType,
          ManagerId,
          ManagerName,
          GroupCode,
          AnalysisTypeCode,
          AnalysisType,
          SubAnalysisType,
          SubActivityType,
          ProposalOrDeliverableName,
          PrincipalInvestigator,
          PrimarySponsor,
          SecondarySponsor,
          AgreementType,
          TransactionType,
          Title,
          Institution,
          Department,
          ProposalOrDeliverableID,
          CompletedDate,
          PendingDate,
          DaysToComplete)
     EXECUTE   @ReturnCode = dbo.RptPCROTurnaroundDetailReport
                         @StartDate          = @StartDate,
                         @EndDate            = @EndDate,
                         @Institution        = @Institution,
                         @Department         = @Department,
                         @Analysistype       = @AnalysisType,
                         @SubAnalysisType    = NULL,
                         @ManagerType        = @ManagerType,
                         @ManagerId          = NULL,
                         @EndCount           = NULL,             -- Get ALL Records.
                         @DomainUserId       = @DomainUserId

     -- check for errors
     SELECT @Error=@@ERROR
     IF @Error != 0 OR @ReturnCode != 0
     BEGIN
          RETURN -1
     END
     
     -- Extract Search Strings for Institution and Department if NON-Zero and Non-Null
     IF @Institution IS NOT NULL AND IsNumeric(@Institution)=1
          SELECT TOP 1 @InstName = Institution
          FROM      @PropsOrDels

     IF @Department IS NOT NULL AND IsNumeric(@Department)=1
          SELECT TOP 1 @DeptName = Department
          FROM      @PropsOrDels
     
     -- Get Grouped Means and Medians
     INSERT    @MeansAndMedians
               (SubAnalysisType,
               SubAnalysisMean,
               SubAnalysisMedian)
     SELECT    SubAnalysisType,
               AVG(DaysToComplete),
               0
     FROM      @PropsOrDels
     GROUP BY  SubAnalysisType

     -- check for errors
     SELECT @Error=@@ERROR
     IF @Error != 0 
     BEGIN
          RETURN -1
     END
     
     -------------------------------------- START MEDIAN (1) PROCESS--------------------------------------
     -- Calculate Medians for each SubAnalysisType
     DECLARE SubTypeCursor  CURSOR FOR 
               SELECT    SubAnalysisType
               FROM      @MeansAndMedians
               
     -- Open and Get each Result
     OPEN SubTypeCursor
 
     
     FETCH NEXT FROM SubTypeCursor INTO @SubAnalysisType
     
     WHILE @@FETCH_STATUS = 0
     BEGIN
          -- Clear table we'll be using
          DELETE FROM @Medians
          
          -- Get Day Counts into simple table
          INSERT    @Medians
                    (DayCounts)
          SELECT    DaysToComplete 
          FROM      @PropsOrDels
          WHERE     SubAnalysisType = @SubAnalysisType
          
          -- Got Days, how many rows inserted?
          -- We need this for Even # of Rows
          SELECT    @RowCount = @@RowCount
          
          IF (@RowCount  % 2) = 1 
              -- USE EASY QUERY FOR ODD # OF ROWS
              SELECT    TOP 1 @Median = DayCounts
              FROM      (SELECT    TOP 50 PERCENT DayCounts
                         FROM      @Medians
                         ORDER BY DayCounts ASC) A
              ORDER BY DayCounts DESC    
          ELSE 
              -- USE MORE COMPLEX QUERY FOR EVEN # OF ROWS
              SELECT     @Median = AVG(CAST(DayCounts AS NUMERIC(20,4)))
              FROM       (SELECT    TOP 2 DayCounts
                         FROM      -- This should get us Half (trunc'd), plus one row.
                                   (SELECT   TOP (@Rowcount/2+1) DayCounts
                                   FROM      @Medians
                                   ORDER BY DayCounts ASC) A
                         ORDER BY DayCounts DESC ) B
          
          -- Update table
          UPDATE    @MeansAndMedians
          SET       SubAnalysisMedian = @Median
          WHERE     SubAnalysisType = @SubAnalysisType
          
          -- Get Next
          FETCH NEXT FROM SubTypeCursor INTO @SubAnalysisType
     END       

     -- Housecleaning
     CLOSE SubTypeCursor       
     DEALLOCATE  SubTypeCursor       

     -------------------------------------- END MEDIAN (1) PROCESS --------------------------------------     

     -------------------------------------- START MEDIAN (2) PROCESS--------------------------------------
     -- Calculate Medians for each Manager & SubAnalysisType
     INSERT    @ManagerSubAnalysisTypeMedians
               (ManagerId,
               SubAnalysisType,
               SubAnalysisMedian)
     SELECT DISTINCT ManagerId,
               SubAnalysisType,
               0
     FROM      @PropsOrDels
          
     DECLARE MgrSubTypeCursor  CURSOR FOR 
               SELECT    ManagerId, SubAnalysisType
               FROM      @ManagerSubAnalysisTypeMedians
               
     -- Open and Get each Result
     OPEN MgrSubTypeCursor

     FETCH NEXT FROM MgrSubTypeCursor INTO @ManagerId, @SubAnalysisType
     
     WHILE @@FETCH_STATUS = 0
     BEGIN
          -- Clear table we'll be using
          DELETE FROM @Medians
          
          -- Get Day Counts into simple table
          INSERT    @Medians
                    (DayCounts)
          SELECT    DaysToComplete 
          FROM      @PropsOrDels
          WHERE     ManagerId = @ManagerId AND 
                    SubAnalysisType = @SubAnalysisType
          
          -- Got Days, how many rows inserted?
          -- We need this for Even # of Rows
          SELECT    @RowCount = @@RowCount
          
          IF (@RowCount  % 2) = 1 
              -- USE EASY QUERY FOR ODD # OF ROWS
              SELECT    TOP 1 @Median = DayCounts
              FROM      (SELECT    TOP 50 PERCENT DayCounts
                         FROM      @Medians
                         ORDER BY DayCounts ASC) A
              ORDER BY DayCounts DESC    
          ELSE 
              -- USE MORE COMPLEX QUERY FOR EVEN # OF ROWS
              SELECT     @Median = AVG(CAST(DayCounts AS NUMERIC(20,4)))
              FROM       (SELECT    TOP 2 DayCounts
                         FROM      -- This should get us Half (trunc'd), plus one row.
                                   (SELECT   TOP (@Rowcount/2+1) DayCounts
                                   FROM      @Medians
                                   ORDER BY DayCounts ASC) A
                         ORDER BY DayCounts DESC ) B
          
          -- Update table
          UPDATE    @ManagerSubAnalysisTypeMedians
          SET       SubAnalysisMedian = @Median
          WHERE     ManagerId = @ManagerId AND 
                    SubAnalysisType = @SubAnalysisType
          
          -- Get Next
          FETCH NEXT FROM MgrSubTypeCursor INTO @ManagerId, @SubAnalysisType
     END       

     -- Housecleaning
     CLOSE MgrSubTypeCursor       
     DEALLOCATE  MgrSubTypeCursor       

     -------------------------------------- END MEDIAN (2) PROCESS --------------------------------------     
     
     -- Return Counts 
     SELECT    CNTS.ManagerType,
               CNTS.AnalysisType,
               CASE LEFT(CNTS.SubAnalysisType,1)
                    WHEN 'C' THEN 1
                    WHEN 'S' THEN 2
                    WHEN 'A' THEN 3
                    ELSE 4
               END AS GroupSort,                    
               CNTS.SubAnalysisType,
               CNTS.ManagerId,
               CNTS.ManagerName,
               SUM(CNTS.[0-30Days]) AS [0-30Days],
               SUM(CNTS.[31-45Days]) AS [31-45Days],
               SUM(CNTS.[46-60Days]) AS [46-60Days],
               SUM(CNTS.[61-89Days]) AS [61-89Days],
               SUM(CNTS.[90+Days]) AS [90+Days],
               SUM(CNTS.[0-30Days]) + 
               SUM(CNTS.[31-45Days]) +
               SUM(CNTS.[46-60Days]) +
               SUM(CNTS.[61-89Days]) + 
               SUM(CNTS.[90+Days]) AS Total,
               AVER.AvgDaysComplete,
               MED.SubAnalysisMedian AS MeanDaysComplete,
               @InstName AS InstSearchName,
               @DeptName AS DeptSearchName,
               M.SubAnalysisMean AS OverallAvgMaxDays,
               M.SubAnalysisMedian AS OverallMeanDays
     FROM      (          
               SELECT    ManagerType,
                         ManagerId,
                         ManagerName,
                         AnalysisType,
                         SubAnalysisType,
                         CASE WHEN DaysToComplete BETWEEN 0 AND 30
                              THEN 1 
                              ELSE 0
                         END AS "0-30Days",
                         CASE WHEN DaysToComplete BETWEEN 31 AND 45
                              THEN 1 
                              ELSE 0
                         END AS "31-45Days",
                         CASE WHEN DaysToComplete BETWEEN 46 AND 60
                              THEN 1 
                              ELSE 0
                         END AS "46-60Days",
                         CASE WHEN DaysToComplete BETWEEN 61 AND 89
                              THEN 1 
                              ELSE 0
                         END AS "61-89Days",
                         CASE WHEN DaysToComplete >= 90
                              THEN 1 
                              ELSE 0
                         END AS "90+Days"
               FROM      @PropsOrDels) CNTS
               
               INNER JOIN 
               (SELECT   ManagerId,
                         AnalysisType,
                         SubAnalysisType,
                         AVG(CAST(DaysToComplete AS NUMERIC(12,4))) AS AvgDaysComplete
               FROM      @PropsOrDels
               GROUP BY  ManagerId,
                         AnalysisType,
                         SubAnalysisType) AVER ON CNTS.ManagerId = AVER.ManagerId AND
                              CNTS.AnalysisType = AVER.AnalysisType AND CNTS.SubAnalysisType = AVER.SubAnalysisType  
               
               INNER JOIN @ManagerSubAnalysisTypeMedians MED ON CNTS.ManagerId = MED.ManagerId AND CNTS.SubAnalysisType = MED.SubAnalysisType
               
               INNER JOIN @MeansAndMedians M ON CNTS.SubAnalysisType = M.SubAnalysisType
               
     GROUP BY  CNTS.ManagerType,
               CNTS.AnalysisType,
               CNTS.SubAnalysisType,
               CNTS.ManagerId,
               CNTS.ManagerName,
               AVER.AvgDaysComplete,
               MED.SubAnalysisMedian,
               M.SubAnalysisMean,
               M.SubAnalysisMedian
     ORDER BY  GroupSort,
               CNTS.AnalysisType,
               CNTS.SubAnalysisType,
               CNTS.ManagerName
     
     -- check for errors
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error != 0 
     BEGIN
          RETURN -1
     END
     
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptPCROTurnaroundReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptPCROTurnaroundReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptPCROTurnaroundReport >>>'
go

GRANT EXECUTE ON dbo.RptPCROTurnaroundReport TO InsightReport,Insight
go


